package mercury.dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import org.apache.log4j.PropertyConfigurator;

import mercury.util.Log;

public class JDBCCatalog implements CatalogDAOInterface {
	
	private String DBMD = "";
	private boolean connected = false;
	private Log logger = new Log(this);
	private Connection connection =null;
	private String prkey = "ID";
	
	public JDBCCatalog() {

	}
	
	public JDBCCatalog(Map m) throws CatalogException {
		init(m);
	}
	
	public boolean isConnectionValid() {
		if(connection != null && connected) {
			return true;
		} else {
			return false;
		}
	}
	
	public void init(Map params) throws CatalogException {
		
		String driver = (String) params.get("driver");
		String url = (String) params.get("url");
		String server = (String) params.get("server");
		String port = (String) params.get("port");
		String database = (String) params.get("database");
		String selectMethod = (String) params.get("selectmethod");
		String uid = (String) params.get("uid");
		String pwd = (String) params.get("pwd");
		if(params.containsKey("primary_key_name")) {
			prkey = (String) params.get("primary_key_name");
		}
		
		String tmpMD = "";

		String connString = url;
		
		if(server != null && server.length() > 0) {
			connString += server;
		}
		
		if(port != null && port.length() > 0) {
			connString += ":"+port+";";
		}
		
		if(database != null && database.length() > 0) {
			connString += "databaseName="+database+";";
		}

		if(selectMethod != null && selectMethod.length() > 0) {
			connString += "selectMethod="+selectMethod+";";
		}
		
		if(uid == null) {
			uid = "";
		}
		
		if(pwd == null) {
			pwd = "";
		}
	
		logger.debug("Attempting to connect to: "+connString);
//		Get connection
		try {
			Object d;
			d = Class.forName(driver).newInstance();
			
			if(d instanceof Driver) {
				DriverManager.registerDriver((Driver) Class.forName(driver).newInstance());
				connection = DriverManager.getConnection(connString,uid,pwd);
				if (connection != null) {
					connected = true;
					logger.debug("Connected to: "+connString);
					logger.debug(getCatalogMetadata());
				} else {
					CatalogException e = new CatalogException("Could not connect to catalog: "+connString);
					logger.error(e);
					logger.trace(e,e);
					throw e;
				}
			} else {
				CatalogException e = new CatalogException("Not an instance of Driver: "+ driver);
				throw e;
			}
		} catch (InstantiationException e) {
			throw new CatalogException(e);
		} catch (IllegalAccessException e) {
			throw new CatalogException(e);
		} catch (ClassNotFoundException e) {
			throw new CatalogException(e);
		} catch (SQLException e) {
			throw new CatalogException(e);
		}
	}
	
	public void listTables() throws CatalogException {
		logger.debug("Attempting to list all tables");
		if(isConnectionValid()){
			try {
		        // Gets the database metadata
		        DatabaseMetaData dbmd = connection.getMetaData();
		    
		        // Specify the type of object; in this case we want tables
		        String[] types = {"TABLE"};
		        ResultSet resultSet = dbmd.getTables(null, null, "%", types);
		    
		        // Get the table names
		        while (resultSet.next()) {
		            // Get the table name
		            String tableName = resultSet.getString(3);
		    
		            // Get the table's catalog and schema names (if any)
		            String tableCatalog = resultSet.getString(1);
		            String tableSchema = resultSet.getString(2);
		            logger.debug("Table: " + tableName);
		            logger.debug("\tCatalog: " + tableCatalog);
		            logger.debug("\tSchema: " + tableSchema);
		        }
		    } catch (SQLException e) {
		    	throw new CatalogException(e);
		    }
		} else {
			throw new CatalogException("Not connected to database");
		}
	}
	
	public String insert(Map map) throws CatalogException {
		logger.debug("Attempting to insert: " + map.toString());
		if(isConnectionValid()) {
			String table = (String) map.get("table");
			Object[] keys = map.keySet().toArray();
			String cols = "";
			String vals = "";
			
			for(int i=0;i<keys.length;i++) {
				String k = (String) keys[i];
				
				if(!k.equals("table") && k.startsWith("col:")) {
					String col = k.substring("col:".length());
					String val = (String) map.get(keys[i]);
					
					if(val != null && !val.equals("null")) {
						if(cols.length() > 0) {
							cols += ", ";
							vals += ", ";
						}
						
						cols += "" + col + "";
						vals += "'" + val + "'";
					}
				}
			}
			
			cols = "(" + cols + ")";
			vals = "(" + vals + ")";
			
			String sql = "INSERT INTO " + table + " " + cols + " VALUES " + vals;
			executeUpdate(sql);
			return lastIDInserted(table);
		} else {
			throw new CatalogException("Not connected to database");
		}
	}

	public void update(Map map) throws CatalogException {
		logger.debug("Attempting to update: " + map.toString());
		if(isConnectionValid()) {
			String table = (String) map.get("table");
			Object[] keys = map.keySet().toArray();
			String set = "";
			String where = "1 = 1";
			
			for(int i=0;i<keys.length;i++) {
				String k = (String) keys[i];
				
				if(!k.equals("table") && (k.startsWith("col:"))) {
					String col = k.substring("col:".length());
					String val = (String) map.get(keys[i]);

					if(set.length() > 0) {
						set += ", ";
					}
					set +=  col + " = '" + val + "'";
					
					
				} else if(!k.equals("table") && (k.startsWith("where_str:"))) {
					String col = k.substring("where_str:".length());
					String val = (String) map.get(keys[i]);

					if(where.length() > 0) {
						where += " AND ";
					}
					
					where += col+ " "+val;
				} else if(!k.equals("table") && (k.startsWith("where:"))) {
					String col = k.substring("where:".length());
					String val = (String) map.get(keys[i]);

					if(where.length() > 0) {
						where += " AND ";
					}
					
					if(col.equals(prkey)) {
						where += col+" = " + val;
					} else {
						where += col+" LIKE '" + val +"'";
					}
				} 
			}
			String sql = "UPDATE " + table + " SET " + set + " WHERE " + where;
			executeUpdate(sql);
		} else {
			throw new CatalogException("Not connected to database");
		}
	}
	
	public void delete(Map map) throws CatalogException {
		logger.debug("Attempting to delete: " + map.toString());
		if(isConnectionValid()) {
			String table = (String) map.get("table");
			Object[] keys = map.keySet().toArray();
			String where = "1 = 1";
			
			for(int i=0;i<keys.length;i++) {
				String k = (String) keys[i];
				if(!k.equals("table") && k.startsWith("col:")) {
					String col = k.substring("col:".length());
					String val = (String) map.get(keys[i]);
					if(where.length() > 0) {
						where += " AND ";
					}
					if(col.equals(prkey)) {
						where += col+" = " + val;
					} else {
						where += col+" LIKE '" + val +"'";
					}
				}
			}
		
			String sql = "DELETE FROM " + table + " WHERE " + where;
			if(!where.equals("1 = 1")) {
				executeUpdate(sql);
			}
		} else {
			throw new CatalogException("Not connected to database");
		}
	}
	
	public String selectValue(Map criteria) throws CatalogException {
		String[][] res = select(criteria);
		if(res.length > 1 && res[0].length>0) {
			logger.debug("Select Value Result=" + res[1][0]);
			return res[1][0];
		} else {
			return "";
		}
	}
	
	public Map selectField(Map criteria) throws CatalogException {
		Map props = new HashMap();
		String[][] res = select(criteria);
		if(res.length > 1) {
			for(int i=0;i<res[0].length;i++) {
				String k = res[0][i];
				String v = res[1][i];
				props.put(k,v);
			}
		}
		logger.debug("Select Field Results=" + props);
		return props;
	}
	
	private String lastIDInserted(String table) throws CatalogException {
		String sql = "SELECT IDENT_CURRENT('" + table + "') AS "+ prkey;
		String id = "";
		if(isConnectionValid()) {
			try {
				String[][] res = executeQuery(sql);
				if(res.length > 1 && res[0].length>0) {
					id = res[1][0];
				} 
			} catch(Exception e) {
				logger.debug("SELECT IDENT_CURRENT does not work, trying last inserted ID");
				id = getMax(prkey,table);
			}
			
		} else {
			throw new CatalogException("Not connected to database");
		}
		return id;
	}
	
	public String getMax(String column, String table) throws CatalogException {
		String maxsql = "SELECT MAX(" + column + ") AS MAXID FROM " + table;
		String[][] maxres = executeQuery(maxsql);
		if(maxres.length > 1 && maxres[0].length>0) {
			return maxres[1][0];
		}
		return "";
	}
	
	public String[][] select(Map criteria) throws CatalogException {
		logger.debug("Attempting to search: " + criteria.toString());
		if(isConnectionValid()) {
			String table = (String) criteria.get("table");
			Object[] keys = criteria.keySet().toArray();
			String cols = "*";
			String where = "1 = 1";
			
			for(int i=0;i<keys.length;i++) {
				String k = (String) keys[i];
				
				if(!k.equals("table") && (k.startsWith("col:"))) {
					String col = k.substring("col:".length());
					String val = (String) criteria.get(keys[i]);

					if(cols.equals("*")) {
						cols = "";
					}
					
					if(cols.length() > 0) {
						cols += ", ";
					} 
					cols +=  col;
				} else if(!k.equals("table") && (k.startsWith("where:"))) {
					String col = k.substring("where:".length());
					String val = (String) criteria.get(keys[i]);

					if(where.length() > 0) {
						where += " AND ";
					}
					if(col.equals(prkey)) {
						where += col+" = " + val;
					} else if(isInteger(val)){
						where += col+" LIKE " + val +"";
					} else {
						where += col+" LIKE '" + val +"'";
					}
				}
			}
			String sql = "SELECT " + cols + " FROM " + table + " WHERE " + where;
			String[][] res = executeQuery(sql);
			
			return res;
		} else {
			throw new CatalogException("Not connected to database");
		}
	}

	
	public String getCatalogMetadata() throws CatalogException {
		String tmpMD = "";
		logger.debug("Attempting to retrieve Database Metadata");
		if(isConnectionValid()) {
			try {
				DatabaseMetaData meta = connection.getMetaData();
				tmpMD += "Driver Information";
				tmpMD += "\nDriver Name: " + meta.getDriverName();
				tmpMD += "\nDriver Version: " + meta.getDriverVersion();
				tmpMD += "\nDatabase Information ";
				tmpMD += "\nDatabase Name: " + meta.getDatabaseProductName();
				tmpMD += "\nDatabase Version: "+  meta.getDatabaseProductVersion();
			} catch (SQLException e) {
				throw new CatalogException(e);
			}
		} else {
			throw new CatalogException("Not connected to database");
		}
		return tmpMD;
	}
	
	private String executeUpdate(String sql) throws CatalogException {
		Statement stmt;
		String id = "-1";
		logger.debug("Attempting to exectue sql: " + sql);
		try {
			stmt = connection.createStatement();
			stmt.executeUpdate(sql);
			return id;
		} catch (SQLException e) {
			throw new CatalogException(e);
		}
	}
	
	private boolean isInteger(String num) {
		try {
			int val = Integer.valueOf(num).intValue();
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	
	private String[][] executeQuery(String sql) throws CatalogException {
		Statement stmt;
		
		try {
			stmt = connection.createStatement();
			String rowcountsql = "SELECT COUNT(*) FROM (" + sql + ") as DERIVEDTBL";
			logger.debug("Attempting to exectue sql: " + rowcountsql);
			ResultSet rssize = stmt.executeQuery(rowcountsql);
			rssize.next();
	        int rows = rssize.getInt(1);
	        logger.debug("Attempting to exectue sql: " + sql);
			ResultSet rs = stmt.executeQuery(sql);
			if(rows > 0) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int cols = rs.getMetaData().getColumnCount();
				String[][] results = new String[rows + 1][cols];
	//			 Get the column names; column indices start from 1
		        for (int i=1; i<cols+1; i++) {
		            results[0][i-1] = rsmd.getColumnName(i);
		        }
				
				int row = 1;
	//			 Fetch each row from the result set
		        while (rs.next()) {
		        	for (int i=1; i<cols+1; i++) {
			            results[row][i -1] = rs.getString(i);
			        }
		        	row += 1;
		        }
		        return results;
	        }

			return new String[0][0];
		} catch (SQLException e) {
			throw new CatalogException(e);
		}
	}
	
	public void closeConnection() throws CatalogException {
		logger.debug("Attempting to close connection");
		if(isConnectionValid()){
			try {
				connection.close();
				connection=null;
				logger.debug("Connection closed");
			} catch (SQLException e) {
				connection=null;
				throw new CatalogException(e);
			}
		}
		connection=null;
	}
	
	public static void main(String args[]) {

		String loggerProps =  "D:\\Program Files\\Apache Software Foundation\\Tomcat 5.5\\webapps\\AppServerAdmin\\WEB-INF\\xml\\logger-console.properties";
		PropertyConfigurator.configure(loggerProps);
		
		// Test
		Map prms = new HashMap();
		prms.put("driver","com.microsoft.jdbc.sqlserver.SQLServerDriver");
		prms.put("url","jdbc:microsoft:sqlserver://");
		prms.put("server","localhost");
		prms.put("port","1433");
		prms.put("database","APPSERVFRMK");
//		 Informs the driver to use server a side-cursor, 
	     // which permits more than one active statement 
	     // on a connection.
		prms.put("selectmethod","cursor");
		prms.put("uid","sa");
		prms.put("pwd","");
		
		Map insrt = new HashMap();
		insrt.put("table","OSTYPES");
		insrt.put("col:name","Solaris");
		insrt.put("col:type","solaris");
		
		Map del = new HashMap();
		del.put("table","OSTYPES");
		del.put("col:type","linux");
		
		Map upd = new HashMap();
		upd.put("table","OSTYPES");
		upd.put("col:name","Linux");
		upd.put("col:type","linux");
		upd.put("where:type","solaris");
		
		Map slc = new HashMap();
		slc.put("table","OSTYPES");
		//slc.put("col:*","");
		slc.put("col:type","linux");
		slc.put("where:type","LINUX");
		
		
		JDBCCatalog cat = new JDBCCatalog();
		try {
			cat.init(prms);
			//cat.listTables();
		//	cat.insert(insrt);
			//cat.delete(del);
			//cat.update(upd);
			String[][] res = cat.select(slc);
			for(int i=0;i<res.length;i++) {
				System.out.print("|");
				for(int j=0;j<res[0].length;j++) {
					System.out.print(res[i][j] + "|");
				}

				System.out.print("\n");
			}
			
			cat.closeConnection();
		} catch (CatalogException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}
